package com.css.zfzx.fwsjcj.modules.statistics.fw.repository.impl;

import com.css.bpm.platform.utils.PlatformBeanUtils;
import com.css.bpm.platform.utils.PlatformObjectUtils;
import com.css.zfzx.fwsjcj.modules.statistics.fw.entity.FwAreaBuildingTemporaryEntity;
import com.css.zfzx.fwsjcj.modules.statistics.fw.repository.FwAreaBuildingNativeReqository;
import com.css.zfzx.fwsjcj.modules.statistics.fw.vo.ProvinceParams;
import com.css.zfzx.fwsjcj.modules.statistics.fw.vo.SelectChinaAndProvinceVo;
import com.css.zfzx.fwsjcj.modules.statistics.fw.vo.SelectStructureVo;
import com.css.zfzx.fwsjcj.modules.statistics.fw.vo.SelectVo;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class FwAreaBuildingNativeReqositoryImpl implements FwAreaBuildingNativeReqository {

    @PersistenceContext
    EntityManager entityManager;

    /**
     * 查询每个结构类型的抽样面积及栋数
     *
     * @param structureType
     * @return
     */
    @Override
    public List<SelectVo> getSamplingAreaAndBuildingByStructureType(String structureType) {
        StringBuilder sql = new StringBuilder("select sum(building_area) samplingArea,count(*) samplingBuilding from  fw_structure_form ");
        StringBuilder whereSql = new StringBuilder("where del_flag = 0 and status != 0 ");
        if (PlatformObjectUtils.isNotEmpty(structureType)) {
            whereSql.append(" and structure_type = :structureType ");
        }

        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(structureType)) {
            query.setParameter("structureType", structureType);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<SelectVo> result = PlatformBeanUtils.copyProperties(list, SelectVo.class);
        return result;
    }

    @Override
    public Double getAllActualAreaByProvince(String province) {
        StringBuilder sql = new StringBuilder("select all_building_area from fw_area_building ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = 0 and area_is_open = 1 ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            whereSql.append(" and district_name = :province ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(province)) {
            query.setParameter("province", province);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        if (list.isEmpty()) {
            return 0.0;
        } else {
            double allActualArea = Double.parseDouble(list.get(0).get("all_building_area").toString());
            return allActualArea;
        }
    }

    @Override
    public Double getAllAmendatoryAreaByProvince(String province) {
        StringBuilder sql = new StringBuilder("select amendatory_all_building_area from fw_area_building ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = 0 and area_is_open = 0 ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            whereSql.append(" and district_name = :province ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(province)) {
            query.setParameter("province", province);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        if (list.isEmpty()) {
            return 0.0;
        } else {
            double allAmendatoryArea = Double.parseDouble(list.get(0).get("amendatory_all_building_area").toString());
            return allAmendatoryArea;
        }
    }

    @Override
    public int getAllActualBuildingByProvince(String province) {
        StringBuilder sql = new StringBuilder("select all_building_count from fw_area_building ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = 0 and count_is_open = 1 ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            whereSql.append(" and district_name = :province ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(province)) {
            query.setParameter("province", province);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        if (list.isEmpty()) {
            return 0;
        } else {
            int proAllActualBuilding = Integer.parseInt(list.get(0).get("all_building_count").toString());
            return proAllActualBuilding;
        }
    }

    @Override
    public int getAllAmendatoryBuildingByProvince(String province) {
        StringBuilder sql = new StringBuilder("select amendatory_all_building_count from fw_area_building ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = 0 and count_is_open = 0 ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            whereSql.append(" and district_name = :province ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(province)) {
            query.setParameter("province", province);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        if (list.isEmpty()) {
            return 0;
        } else {
            int proAllAmendatoryBuilding = Integer.parseInt(list.get(0).get("amendatory_all_building_count").toString());
            return proAllAmendatoryBuilding;
        }

    }

    /**
     * 查询每个省的抽样面积及栋数
     *
     * @param province
     * @return
     */
    @Override
    public List<SelectVo> getProSamplingAreaAndBuilding(String province) {
        StringBuilder sql = new StringBuilder("select sum(building_area) samplingArea,count(*) samplingBuilding from  fw_structure_form ");
        StringBuilder whereSql = new StringBuilder("where del_flag = 0 and status != 0 ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            whereSql.append(" and belongto_province = :province ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(province)) {
            query.setParameter("province", province);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<SelectVo> result = PlatformBeanUtils.copyProperties(list, SelectVo.class);
        return result;
    }

    /**
     * 查询全国相关数据
     *
     * @return
     */
    @Override
    public List<SelectChinaAndProvinceVo> getChinaData(String divisionLevel) {
        StringBuilder sql = new StringBuilder("select id,data_time,district_name,area_data,area_sampling,count_data,count_sampling from fw_area_building_temporary ");
        StringBuilder whereSql = new StringBuilder(" WHERE data_time=(select max(data_time) from fw_area_building_temporary WHERE del_flag = 0 and is_structure = 0) and district_name = '全国' and del_flag = 0 and is_structure = 0");
        if (PlatformObjectUtils.isNotEmpty(divisionLevel)) {
            whereSql.append(" and division_level = :divisionLevel ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(divisionLevel)) {
            query.setParameter("divisionLevel", divisionLevel);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<SelectChinaAndProvinceVo> result = PlatformBeanUtils.copyProperties(list, SelectChinaAndProvinceVo.class);
        return result;
    }

    /**
     * 查询结构类型数据
     *
     * @return
     */
    @Override
    public List<SelectStructureVo> getStructureData() {
        StringBuilder sql = new StringBuilder("select id,data_time,structure_name,structure_area_data,structure_area_proportion,structure_count_data,structure_count_proportion from fw_area_building_temporary ");
        StringBuilder whereSql = new StringBuilder(" WHERE data_time=(select max(data_time) from fw_area_building_temporary WHERE del_flag = 0 and is_structure = 1) and del_flag = 0  and is_structure = 1 and district_name = '全国'");
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<SelectStructureVo> result = PlatformBeanUtils.copyProperties(list, SelectStructureVo.class);
        return result;
    }

    /**
     * 查询省数据
     *
     * @return
     */
    @Override
    public List<SelectChinaAndProvinceVo> getProvinceData(String divisionLevel) {
        StringBuilder sql = new StringBuilder("select id,data_time,district_name,area_data,area_sampling,count_data,count_sampling from fw_area_building_temporary ");
        StringBuilder whereSql = new StringBuilder(" WHERE data_time=(select max(data_time) from fw_area_building_temporary WHERE del_flag = 0 and is_structure = 0) and del_flag = 0 and is_structure = 0 and district_name != '全国' ");
        StringBuilder orderSql = new StringBuilder(" order by division_code");
        if (PlatformObjectUtils.isNotEmpty(divisionLevel)) {
            whereSql.append(" and division_level = :divisionLevel ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).append(orderSql).toString());
        if (PlatformObjectUtils.isNotEmpty(divisionLevel)) {
            query.setParameter("divisionLevel", divisionLevel);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<SelectChinaAndProvinceVo> result = PlatformBeanUtils.copyProperties(list, SelectChinaAndProvinceVo.class);
        return result;
    }

    /**
     * 查询全国的抽样总面积及抽样总栋数
     *
     * @return
     */
    @Override
    public List<SelectVo> getAllAreaAndBuildingCount() {
        StringBuilder sql = new StringBuilder("select sum(building_area) samplingArea,count(*) samplingBuilding from fw_structure_form ");
        StringBuilder whereSql = new StringBuilder("where del_flag = 0 and status != 0 ");
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<SelectVo> result = PlatformBeanUtils.copyProperties(list, SelectVo.class);
        return result;
    }

    /**
     * 查询总面积修正值是否启用
     *
     * @param province
     * @return
     */
    @Override
    public String getAreaIsOpen(String province) {
        StringBuilder sql = new StringBuilder("select area_is_open from fw_area_building ");
        StringBuilder whereSql = new StringBuilder("where del_flag = 0 ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            whereSql.append(" and district_name = :province ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(province)) {
            query.setParameter("province", province);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        if (list.isEmpty()) {
            return "1";
        } else {
            String areaIsOpen = list.get(0).get("area_is_open").toString();
            return areaIsOpen;
        }
    }

    /**
     * 查询总栋数修正值是否启用
     *
     * @param province
     * @return
     */
    @Override
    public String getCountIsOpen(String province) {
        StringBuilder sql = new StringBuilder("select count_is_open from fw_area_building ");
        StringBuilder whereSql = new StringBuilder("where del_flag = 0 ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            whereSql.append(" and district_name = :province ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(province)) {
            query.setParameter("province", province);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        if (list.isEmpty()) {
            return "1";
        } else {
            String countIsOpen = list.get(0).get("count_is_open").toString();
            return countIsOpen;
        }
    }

    /**
     * 查询省详情数据
     *
     * @return
     */
    @Override
    public List<ProvinceParams> queryProvinceFwData() {
        StringBuilder sql = new StringBuilder("select district_name,all_building_area,amendatory_all_building_area,area_is_open,all_building_count,amendatory_all_building_count,count_is_open from fw_area_building ");
        StringBuilder whereSql = new StringBuilder("where del_flag = 0 and (division_level = 0 or division_level = 1) order by division_code");
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<ProvinceParams> result = PlatformBeanUtils.copyProperties(list, ProvinceParams.class);
        return result;
    }

    @Override
    public List<SelectChinaAndProvinceVo> getChinaProvinceData(String divisionLevel) {
        StringBuilder sql = new StringBuilder("select id,data_time,district_name,area_data value,area_sampling,count_data,count_sampling from fw_area_building_temporary ");
        StringBuilder whereSql = new StringBuilder(" WHERE data_time = (select max(data_time) from fw_area_building_temporary WHERE del_flag = 0 and is_structure = 0) and del_flag = 0 and is_structure = 0");
        if (PlatformObjectUtils.isNotEmpty(divisionLevel)) {
            whereSql.append(" and division_level = :divisionLevel ");
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(divisionLevel)) {
            query.setParameter("divisionLevel", divisionLevel);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        List<SelectChinaAndProvinceVo> result = PlatformBeanUtils.copyProperties(list, SelectChinaAndProvinceVo.class);
        return result;
    }

    @Override
    public int getIsData(String districtName, String belongtoCity) {
        StringBuilder sql = new StringBuilder("select count(*) count from fw_area_building ");
        StringBuilder whereSql = new StringBuilder(" where del_flag = 0 ");
        if(districtName.equals("全国")){
            whereSql.append("and district_name = :districtName ");
        }else{
            if(PlatformObjectUtils.isNotEmpty(districtName) && PlatformObjectUtils.isEmpty(belongtoCity)){
                whereSql.append(" and district_name = :districtName and division_level = 1");
            }else if(PlatformObjectUtils.isNotEmpty(districtName) && PlatformObjectUtils.isNotEmpty(belongtoCity)){
                whereSql.append(" and district_name = :districtName and belongto_city = :belongtoCity and division_level = 2");
            }
        }
        Query query = this.entityManager.createNativeQuery(sql.append(whereSql).toString());
        if (PlatformObjectUtils.isNotEmpty(districtName)) {
            query.setParameter("districtName", districtName);
        }
        if (PlatformObjectUtils.isNotEmpty(belongtoCity)) {
            query.setParameter("belongtoCity", belongtoCity);
        }
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.getResultList();
        String count = list.get(0).get("count").toString();
        return Integer.parseInt(count);
    }
}
